package cc.admin.common.db.table.base;

import cc.admin.common.db.table.entity.DbColumn;
import cc.admin.common.db.table.entity.DbColumnType;
import cc.admin.common.db.table.entity.DbEntity;
import cc.admin.common.db.table.entity.MetaSql;
import cc.admin.common.db.table.reader.MysqlReader;
import cc.admin.common.db.table.reader.OracleReader;
import cc.admin.common.db.table.reader.SqlServerReader;
import cc.admin.common.db.util.DbConstants;
import cc.admin.common.util.dynamic.db.DynamicDBUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Entity;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.google.common.collect.ImmutableList;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
 * @author: ZhangHouYing
 * @date: 2018-06-24
 */
@Slf4j
@Data
public abstract class EtlReader extends BaseEtl {

	private static final List<String> NUMBER_COLUMN_LIST = ImmutableList.of("FLOAT", "BIT", "BIGINT", "TINYINT", "NUMERIC", "SMALLINT", "INT", "DOUBLE", "DECIMAL");
	protected String dbType;
	protected String owner;
	protected String tableName;

	protected MetaSql metaSql = new MetaSql();

	public List<Entity> allTableList;
	public List<Entity> allTableCommentList;
	public List<Entity> allColumnList;
	public List<Entity> allColumnCommentList;
	public List<Entity> allPkList;

	protected JdbcTemplate jdbcTemplate;

	/**
	 * 根据jdbcTemplate初始化
	 *
	 * @param jdbcTemplate
	 * @return
	 */
	public static EtlReader getInstance(JdbcTemplate jdbcTemplate) {
		EtlReader etlReader = null;
		String databaseProductName = getDatabaseProductName(jdbcTemplate);
		switch (databaseProductName){
			case DbConstants.DB_NAME_MYSQL :
				etlReader = new MysqlReader();
				break;
			case DbConstants.DB_NAME_ORACLE :
				etlReader = new OracleReader();
				break;
			case DbConstants.DB_NAME_SQLSERVER:
				etlReader = new SqlServerReader();
				break;
			default: //可选
				throw new RuntimeException("不能识别的etlReader:" + databaseProductName);
		}
		etlReader.setJdbcTemplate(jdbcTemplate);
		etlReader.setDbType(databaseProductName);
		etlReader.init();
		return etlReader;
	}

	/**
	 * 根据dbKey初始化
	 *
	 * @param dbKey
	 * @return
	 */
	public static EtlReader getInstance(String dbKey) {
		return getInstance(DynamicDBUtil.getJdbcTemplate(dbKey, false));
	}


	/**
	 * 读取数据库所有实体信息
	 *
	 * @return
	 * @throws SQLException
	 */
	public List<DbEntity> readEntityList() {
		List<DbEntity> dbEntityList = new ArrayList<>();
		for (Entity table : this.allTableList) {
			String tableName = table.getStr("tableName");
			String tableComment = this.getTableComment(tableName);
			DbEntity dbEntity = new DbEntity(tableName, tableComment);
			//dbEntity.setDbColumnList(getDataColumnList(tableName));
			dbEntityList.add(dbEntity);
		}
		return dbEntityList;
	}

	/**
	 * 读取表元数据
	 *
	 * @param tableName
	 * @return
	 */
	public DbEntity readEntity(String tableName) {
		if (StrUtil.isEmpty(tableName)) {
			throw new RuntimeException("表名为空");
		}
		List<DbEntity> dbEntityList = readEntityList();
		for (DbEntity table : dbEntityList) {
			if (tableName.equalsIgnoreCase(table.getId())) {
				table.setDbColumnList(readColumnList(tableName));
				return table;
			}
		}
		throw new RuntimeException("表不存在:" + tableName);
	}

	/**
	 * 获取数据库列信息
	 *
	 * @param tableName
	 * @return
	 */
	public List<DbColumn> readColumnList(String tableName) {
		List<DbColumn> dbColumnList = new ArrayList<>();
		int order = 1;
		for (Entity column : this.allColumnList) {
			if (tableName.equals(column.getStr("tableName"))) {
				DbColumn dbColumn = new DbColumn();
				dbColumn.setEntity(tableName);
				String columnName = column.getStr("columnName");
				dbColumn.setCode(columnName);
				dbColumn.setName(getColumnComment(tableName, columnName));
				dbColumn.setDbColumnType(getColumnType(column));
				dbColumn.setIsKey(getIsPk(tableName, columnName));
				dbColumn.setLength(getLength(column));
				dbColumn.setScale(column.getInt("dataScale"));
				dbColumn.setNullable(column.getStr("nullable"));
				dbColumn.setSortOrder(order++);
				dbColumnList.add(dbColumn);
			}
		}
		return dbColumnList;
	}

	/**
	 * 我们希望尽量减轻数据库压力，一次只访问一次就把所有的数据都获取过来
	 */
	public void init() {
		this.owner = jdbcTemplate.queryForObject(getDbNameSql(), String.class);
		this.initAllSql();
		this.allTableList = initBySql(this.metaSql.getTableSql());
		this.allTableCommentList = initBySql(this.metaSql.getTableCommentSql());
		this.allColumnList = initBySql(this.metaSql.getColumnSql());
		this.allColumnCommentList = initBySql(this.metaSql.getColumnCommentSql());
		this.allPkList = initBySql(this.metaSql.getPkSql());
	}

	/**
	 * 查询数据库名称SQL
	 *
	 * @return
	 */
	protected abstract String getDbNameSql();

	/**
	 * 这里需要将数据库元数据查询SQL准备好
	 */
	protected abstract void initAllSql();

	/**
	 * 根据sql初始化数据列表
	 *
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public List<Entity> initBySql(String sql) {
		List<Entity> resultList = new ArrayList<>();
		log.info("sql:{}", sql);
		List<Map<String, Object>> entityList = readData(sql);
		for (Map<String, Object> entity : entityList) {
			Entity e = Entity.create();
			for (String field : entity.keySet()) {
				Object value = entity.get(field);
				if (value instanceof byte[]) {
					value = new String((byte[]) value);
				}
				e.put(StrUtil.toCamelCase(field.toLowerCase()), value);
			}
			resultList.add(e);
		}
		return resultList;
	}

	public List<Map<String, Object>> readData(String sql) {
		return jdbcTemplate.queryForList(sql);
	}


	/**
	 * 获取列类型
	 *
	 * @param column
	 * @return
	 */
	public DbColumnType getColumnType(Entity column) {
		return DbColumnType.convert(column.getStr("dataType"));
	}


	/**
	 * 获取列长度
	 *
	 * @param column
	 * @return
	 */
	public Integer getLength(Entity column) {
		if (NUMBER_COLUMN_LIST.contains(column.getStr("dataType"))) {
			return column.getInt("dataPrecision");
		}
		return column.getInt("dataLength");
	}

	/**
	 * 字段是否是主键
	 *
	 * @param tableName
	 * @param columnName
	 * @return
	 */
	public String getIsPk(String tableName, String columnName) {
		for (Entity pk : this.allPkList) {
			if (tableName.equals(pk.getStr("tableName")) && columnName.equals(pk.getStr("columnName"))) {
				return "Y";
			}
		}
		return "N";
	}

	/**
	 * 获取列注释
	 *
	 * @param tableName
	 * @return
	 */
	public String getColumnComment(String tableName, String columnName) {
		for (Entity columnComment : this.allColumnCommentList) {
			if (tableName.equals(columnComment.getStr("tableName")) && columnName.equals(columnComment.getStr("columnName"))) {
				return columnComment.getStr("comments");
			}
		}
		return "";
	}

	/**
	 * 获取表注释
	 *
	 * @param tableName
	 * @return
	 */
	public String getTableComment(String tableName) {
		for (Entity tableComment : this.allTableCommentList) {
			if (tableName.equals(tableComment.getStr("tableName"))) {
				return tableComment.getStr("comments");
			}
		}
		return "";
	}

	/**
	 * 获取分页SQL
	 * @param tableName
	 * @param cnd
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	public String getPageDataSql(String tableName, String cnd, int pageNo, int pageSize) {
		StringBuilder sb = new StringBuilder();
		sb.append("select * from ").append(tableName);
		if (StrUtil.isNotEmpty(cnd)) {
			sb.append(" where ").append(cnd);
		}
		return getPageDataSql(sb.toString(), pageNo, pageSize);
	}

	/**
	 * 获取分页SQL
	 *
	 * @param sql
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	public abstract String getPageDataSql(String sql, int pageNo, int pageSize);

	/**
	 * 获取指定表的指定页数据
	 *
	 * @param tableName
	 * @param cnd
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	public List<Map<String, Object>> getDataList(String tableName, String cnd, int pageNo, int pageSize) {
		String sql = getPageDataSql(tableName, cnd, pageNo, pageSize);
		return readData(sql);

	}
	/**
	 * 读取数据库中的数据
	 * @param tableName
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	public Page<Map<String, Object>> getPageData(String tableName, String cnd, int pageNo, int pageSize) {
		String sql = getPageDataSql(tableName, cnd, pageNo, pageSize);
		return getPageData(sql, pageNo, pageSize);
	}

	/**
	 * 根据SQL语句查询
	 *
	 * @param sql
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	public Page<Map<String, Object>> getPageData(String sql, int pageNo, int pageSize) {
		Page<Map<String, Object>> paginate = new Page<>();
		log.info("Read sql:{}", sql);
		List<Map<String, Object>> dataList = readData(sql);
		paginate.setRecords(dataList);
		int total = getTotal(sql);
		paginate.setTotal(total);
		return paginate;
	}

	/**
	 * 查询总数
	 *
	 * @param sql 原始SQL
	 * @return
	 */
	public Integer getTotal(String sql) {
		return jdbcTemplate.queryForObject(String.format("select count(1) from (%s) t",sql), Integer.class);
	}

}
